USE Liberty
GO

-- ------------------------------------------------------------------------------------------------
-- Visits Table Create Script
-- ------------------------------------------------------------------------------------------------
SET NOCOUNT OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

PRINT 'Creating Table dbo.Visits...'
GO

IF OBJECT_ID('dbo.Visits', 'U') IS NULL
BEGIN
	CREATE TABLE dbo.Visits
	(OID						bigint		IDENTITY(1, 1)	NOT NULL
	,Version					rowversion
	,CaseID						bigint						NOT NULL
	,ArrivalDate				datetime						NULL
	,DischargeDate				datetime						NULL
	,FacilityID					bigint						NOT NULL
	,EstimatedLOS				int								NULL	-- Always in hours
	,FamilyPresent				bit							NOT NULL
	,FamilyArrivalDate			datetime						NULL
	,FamilyDepartureDate		datetime						NULL
	,FamilyEstimatedLOS			int								NULL	-- Always in hours
	,MethodOfTravel				varchar(128)					NULL
	,OriginOfTravel				varchar(128)					NULL
	,InvitationalTravelOrders	bit								NULL
	,UpdatedDate				datetime					NOT NULL
		CONSTRAINT DFLT_Visits_UpdatedDate DEFAULT GETDATE()
	,CreatedDate				datetime					NOT NULL
		CONSTRAINT DFLT_Visits_CreatedDate DEFAULT GETDATE()

	,CONSTRAINT PK_Visits PRIMARY KEY CLUSTERED( OID ) --ON PRIMARY

	,CONSTRAINT FK_Visits_Cases FOREIGN KEY (CaseID)
		REFERENCES Cases(OID)

	,CONSTRAINT FK_Visits_Facilities FOREIGN KEY (FacilityID)
		REFERENCES Facilities(OID)

	)

	PRINT 'Table dbo.Visits has been created successfully.'

END
ELSE 
BEGIN
    PRINT 'Table dbo.Visits already exists.'
END

GO
